import sqlite3

# 直接连接到SQLite数据库
conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# 函数：检查列是否存在
def column_exists(table_name, column_name):
    cursor.execute(f"""SELECT name FROM pragma_table_info('{table_name}') WHERE name='{column_name}';""")
    result = cursor.fetchone()
    return result is not None

# 检查user表是否存在
table_exists = False
try:
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='user';")
    table_exists = cursor.fetchone() is not None

except Exception as e:
    print(f'检查表是否存在时出错: {e}')

# 如果表存在
if table_exists:
    # 检查并添加password列
    if not column_exists('user', 'password'):
        try:
            cursor.execute("ALTER TABLE user ADD COLUMN password VARCHAR(200);")
            print('password列添加成功')
        except Exception as e:
            print(f'添加password列时出错: {e}')
    else:
        print('password列已存在')
    
    # 检查并添加is_admin列
    if not column_exists('user', 'is_admin'):
        try:
            cursor.execute("ALTER TABLE user ADD COLUMN is_admin BOOLEAN DEFAULT 0;")
            print('is_admin列添加成功')
        except Exception as e:
            print(f'添加is_admin列时出错: {e}')
    else:
        print('is_admin列已存在')
    
    # 检查并添加admin_level列
    if not column_exists('user', 'admin_level'):
        try:
            cursor.execute("ALTER TABLE user ADD COLUMN admin_level INTEGER DEFAULT 0;")
            print('admin_level列添加成功')
            
            # 将已有的管理员用户设置为超级管理员
            cursor.execute("UPDATE user SET admin_level = 2 WHERE is_admin = 1;")
            print('已将现有管理员设置为超级管理员')
        except Exception as e:
            print(f'添加admin_level列时出错: {e}')
    else:
        print('admin_level列已存在')
    
    # 提交更改
    conn.commit()
    print('数据库表结构更新完成')
else:
    print('user表不存在')

# 关闭连接
cursor.close()
conn.close()

print('脚本执行完成')